﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Sinacor.Infra.Tools.ServiceRegistration.Data
{
    public class Service : ResourceAccess
    {
        #region Methods

        public List<Entity.Service> SelectService(Entity.Service entity, bool addCharacterLike)
        {
            List<Entity.Service> response = null;

            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("    COD_SERV, ");
            sql.AppendLine("    COD_SUB_MOD, ");
            sql.AppendLine("    NOME_SERV, ");
            sql.AppendLine("    DESC_SERV, ");
            sql.AppendLine("    IC_STATUS, ");
            sql.AppendLine("    DATA_ALTE, ");
            sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD) DATA_ALTE_MAX ");
            sql.AppendLine("FROM ");
            sql.AppendLine("    TFWDSERV ");
            sql.AppendLine("WHERE ");
            sql.AppendLine("    (COD_SERV = :COD_SERV OR :COD_SERV IS NULL) AND ");
            sql.AppendLine("    (COD_SUB_MOD = :COD_SUB_MOD OR :COD_SUB_MOD IS NULL) AND ");
            sql.AppendLine("    (UPPER(NOME_SERV) LIKE UPPER(:NOME_SERV) OR :NOME_SERV IS NULL) AND ");
            sql.AppendLine("    (UPPER(DESC_SERV) LIKE UPPER(:DESC_SERV) OR :DESC_SERV IS NULL) AND ");
            sql.AppendLine("    (IC_STATUS = :IC_STATUS OR :IC_STATUS IS NULL) ");
            sql.AppendLine("ORDER BY NOME_SERV");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_SERV", DbType.Int32, entity.ServiceId);
                this.AddInParameter(command, "COD_SUB_MOD", DbType.String, entity.SubModule != null ? entity.SubModule.SubModuleId : null);
                this.AddInParameter(command, "NOME_SERV", DbType.String, addCharacterLike == true ? AddCharacterLike(entity.ServiceName) : entity.ServiceName);
                this.AddInParameter(command, "DESC_SERV", DbType.String, addCharacterLike == true ? AddCharacterLike(entity.Description) : entity.Description);
                this.AddInParameter(command, "IC_STATUS", DbType.String, entity.StatusApproval.HasValue ? entity.StatusApproval == Entity.StatusApproval.Aprovado ? "0" : "1" : null);

                response = this.FindAll<Entity.Service>(command,
                    (
                        reader => new Entity.Service
                        {
                            ServiceId = reader.GetValue<int>("COD_SERV"),
                            ServiceName = reader.GetValue<string>("NOME_SERV"),
                            Description = reader.GetValue<string>("DESC_SERV"),
                            Methods = GetMethods(reader.GetValue<int>("COD_SERV")),
                            SubModule = entity.SubModule != null ? entity.SubModule : GetSubModule(reader.GetValue<int>("COD_SUB_MOD")),
                            EndPoints = GetEndPoints(reader.GetValue<int>("COD_SERV")),
                            StatusApproval = reader.GetValue<string>("IC_STATUS") == "0" ? Entity.StatusApproval.Aprovado : Entity.StatusApproval.AguardandoAprovacao,
                            DateChange = reader.GetValue<DateTime>("DATA_ALTE"),
                            IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX")),
                        }
                    )
                );
            }

            return response;
        }

        public Entity.Service InsertService(Entity.Service entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("INSERT INTO TFWDSERV ( ");
            sql.AppendLine("    COD_SERV, ");
            sql.AppendLine("    COD_SUB_MOD, ");
            sql.AppendLine("    NOME_SERV, ");
            sql.AppendLine("    DESC_SERV, ");
            sql.AppendLine("    IC_STATUS, ");
            sql.AppendLine("    DATA_ALTE ");
            sql.AppendLine("    ) VALUES ( ");
            sql.AppendLine("    :COD_SERV, ");
            sql.AppendLine("    :COD_SUB_MOD, ");
            sql.AppendLine("    :NOME_SERV, ");
            sql.AppendLine("    :DESC_SERV, ");
            sql.AppendLine("    :IC_STATUS, ");

            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine("    :DATA_ALTE) ");
            else
                sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_SERV", DbType.Int32, entity.ServiceId);
                this.AddInParameter(command, "COD_SUB_MOD", DbType.String, entity.SubModule != null ? entity.SubModule.SubModuleId : null); 
                this.AddInParameter(command, "NOME_SERV", DbType.String, entity.ServiceName);
                this.AddInParameter(command, "DESC_SERV", DbType.String, entity.Description);
                this.AddInParameter(command, "IC_STATUS", DbType.String, entity.StatusApproval == Entity.StatusApproval.Aprovado ? "0" : "1");

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }

            return entity;
        }

        public void UpdateService(Entity.Service entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("UPDATE TFWDSERV SET ");
            sql.AppendLine("COD_SUB_MOD = :COD_SUB_MOD, ");
            sql.AppendLine("NOME_SERV = :NOME_SERV, ");
            sql.AppendLine("DESC_SERV = :DESC_SERV, ");
            sql.AppendLine("IC_STATUS = :IC_STATUS ");

            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine(", DATA_ALTE = :DATA_ALTE ");

            sql.AppendLine("WHERE COD_SERV = :COD_SERV ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_SERV", DbType.Int32, entity.ServiceId);
                this.AddInParameter(command, "COD_SUB_MOD", DbType.String, entity.SubModule != null ? entity.SubModule.SubModuleId : null);
                this.AddInParameter(command, "NOME_SERV", DbType.String, entity.ServiceName);
                this.AddInParameter(command, "DESC_SERV", DbType.String, entity.Description);
                this.AddInParameter(command, "IC_STATUS", DbType.String, entity.StatusApproval == Entity.StatusApproval.Aprovado ? "0" : "1");

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }
        }

        public void DeleteService(Entity.Service entity)
        {
            Method m = new Method();
            EndPoint ep = new EndPoint();
            string sql = "DELETE FROM TFWDSERV WHERE COD_SERV = :COD_SERV";

            m.DeleteMethodByServiceId(new Entity.Method { Service = new Entity.Service { ServiceId = entity.ServiceId } } );
            ep.DeleteEndPointByServiceId(new Entity.EndPoint { Service = new Entity.Service { ServiceId = entity.ServiceId } });

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                this.AddInParameter(command, "COD_SERV", DbType.Int32, entity.ServiceId);
                this.ExecuteNonQuery(command);
            }
        }

        public void DeleteServiceBySubModuleId(Entity.Service entity)
        {
            Method m = new Method();
            EndPoint ep = new EndPoint();
            string sql = "DELETE FROM TFWDSERV WHERE COD_SUB_MOD = :COD_SUB_MOD";

            foreach (Entity.Service item in this.SelectService(entity, false))
            {
                m.DeleteMethodByServiceId(new Entity.Method { Service = item });
            }

            foreach (Entity.Service item in this.SelectService(entity, false))
            {
                ep.DeleteEndPointByServiceId(new Entity.EndPoint { Service = item });
            }

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                this.AddInParameter(command, "COD_SUB_MOD", DbType.Int32, entity.SubModule.SubModuleId);
                this.ExecuteNonQuery(command);
            }
        }

        public int GetNextServiceId()
        {
            int retorno = 0;
            string sql = " SELECT NVL(MAX(COD_SERV) + 1, 1) FROM TFWDSERV ";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                retorno = Convert.ToInt32(this.ExecuteScalar(command));
            }

            return retorno;
        }

        private Entity.SubModule GetSubModule(int sub_module_id)
        {
            SubModule s = new SubModule();
            List<Entity.SubModule> responseList = null;
            Entity.SubModule response = null;

            if (sub_module_id != 0)
            {
                responseList = s.SelectSubModule(new Entity.SubModule { SubModuleId = sub_module_id });

                if (responseList != null && responseList.Count > 0)
                    response = responseList[0];
            }

            return response;
        }

        private List<Entity.Method> GetMethods(int id_service)
        {
            Method t = new Method();
            List<Entity.Method> response = null;

            if (id_service != 0)
            {
                response = t.SelectMethod(new Entity.Method { Service = new Entity.Service { ServiceId = id_service } }, false);
            }

            return response;
        }

        private List<Entity.EndPoint> GetEndPoints(int id_service)
        {
            EndPoint t = new EndPoint();
            List<Entity.EndPoint> response = null;

            if (id_service != 0)
            {
                response = t.SelectEndPoint(new Entity.EndPoint { Service = new Entity.Service { ServiceId = id_service } }, false);
            }

            return response;
        }

        #endregion
    }
}
